--use alerts
set nocount on 
print '/**********************************************************************************************'
select 	cast(@@servername as varchar(30)) as [Server], 
	cast(db_name() as varchar(30)) as [Database], 
	cast(getdate()as char(20)) as [Script Created Date-Time]

select cast(name as varchar(50)) as [All users in Database -- members of the public role]
from sysusers 
where islogin = 1
order by isntuser

select case when grouping(groupuid) = 1 and grouping(memberuid) = 1  
		then ''
	    when grouping(groupuid) = 0 and grouping(memberuid) = 1  
		then cast(user_name(groupuid) as varchar(20))
	    when grouping(groupuid) = 0 and grouping(memberuid) = 0				
		then ''
		end as role,
	case when grouping(groupuid) = 1 and grouping(memberuid) = 1  
		then ''
	     when grouping(groupuid) = 0 and grouping(memberuid) = 1  
		then ''
	     when grouping(groupuid) = 0 and grouping(memberuid) = 0  	
		then cast(user_name(memberuid) as nvarchar(30)) 
		end as members,
	case when grouping(groupuid) = 1 and grouping(memberuid) = 1  
		then NULL
	     when grouping(groupuid) = 0 and grouping(memberuid) = 1  
		then NULL
	     when grouping(groupuid) = 0 and grouping(memberuid) = 0  	
		then (select sid from sysusers where uid = memberuid)
		end as sid
from sysmembers
group by groupuid, memberuid
with rollup
order by groupuid, memberuid

print '**********************************************************************************************/
'
print 'use ' + db_name()

select cast('if (select suser_sid(''' + suser_sname(u.sid) + ''')) is null 	 
	exec sp_addlogin [' + suser_sname(u.sid) + '], [' + suser_sname(u.sid) + '], [' + db_name(l.dbid) + '],''' 
	+ l.language 
	+ ''',
' as varchar(120)) as [-- Add sql logins script], coalesce(u.sid,0x0)
from sysusers u
inner join master.dbo.sysxlogins l
on u.sid = l.sid
where u.islogin = 1
and u.isntname = 0
and u.name not in ('dbo','guest')

select cast('exec sp_grantlogin [' + name + ']' as varchar(120)) as [-- Add Windows logins script] 
from sysusers 
where islogin = 1
and isntname = 1
and name not in ('dbo')

select cast('if (select user_id(''' + name + ''')) is null 	 
	exec sp_adduser [' + name + ']' as varchar(120)) as [-- Add users script]
from sysusers 
where islogin = 1
and isntname = 0
and name not in ('dbo','guest')
union
select cast('exec sp_grantdbaccess [' + name + ']' as varchar(120)) as [-- Add users script] 
from sysusers 
where islogin = 1
and isntname = 1
and name not in ('dbo')

select distinct 'if (select user_id(''' + cast(user_name(groupuid) as varchar(50))+ ''')) is null
	exec sp_addrole [' + cast(user_name(groupuid) as varchar(50)) + ']' as [-- Add roles script]
from sysmembers
where groupuid > 16399

select 'exec sp_addrolemember [' +
	cast(user_name(groupuid) as varchar(50)) + '], [' +
	cast (user_name(memberuid) as varchar(50)) + ']' as [-- Add role members script]
from sysmembers
where user_name(memberuid) not in ('dbo','guest') 


select  cast(type.name as varchar(10)) + ' ' +
	cast(action.name as varchar(20)) + ' ON [' +
	cast(user_name(o.uid) as varchar(20)) + '].[' +
	cast(o.name as varchar(50)) + '] TO [' + 
	cast(user_name(p.uid) as varchar(80)) + ']' as [-- Procedure Permissions]
from sysprotects p
inner join sysobjects o
on p.id = o.id
inner join master.dbo.spt_values type
on p.protecttype = type.number
inner join master.dbo.spt_values action
on p.action = action.number
where type.type = 'T'
and action.type = 'T'
and o.Type = 'P'
and o.status > 0
order by o.type, o.name

select  cast(type.name as varchar(10)) + ' ' +
	cast(action.name as varchar(20)) + ' ON [' +
	cast(user_name(o.uid) as varchar(20)) + '].[' +
	cast(o.name as varchar(50)) + '] TO [' + 
	cast(user_name(p.uid) as varchar(80)) + ']' as [-- Table permissions]
from sysprotects p
inner join sysobjects o
on p.id = o.id
inner join master.dbo.spt_values type
on p.protecttype = type.number
inner join master.dbo.spt_values action
on p.action = action.number
where type.type = 'T'
and action.type = 'T'
and o.Type = 'U'
and o.status > 0
order by o.type, o.name

select  o.Type [-- Type],
	cast(type.name as varchar(10)) + ' ' +
	cast(action.name as varchar(20)) + ' ON [' +
	cast(user_name(o.uid) as varchar(20)) + '].[' +
	cast(o.name as varchar(50)) + '] TO [' + 
	cast(user_name(p.uid) as varchar(80)) + ']' as [Other object level permissions]
from sysprotects p
inner join sysobjects o
on p.id = o.id
inner join master.dbo.spt_values type
on p.protecttype = type.number
inner join master.dbo.spt_values action
on p.action = action.number
where type.type = 'T'
and action.type = 'T'
and o.Type not in ('P','U')
and o.status > 0
order by o.type, o.name

select  cast(type.name as varchar(10)) + '  ' +
	cast(action.name as varchar(20)) + '  to [' +
	cast(user_name(p.uid) as varchar(80)) + ']' as [-- Database Level permissions]
from sysprotects p
inner join master.dbo.spt_values type
on p.protecttype = type.number
inner join master.dbo.spt_values action
on p.action = action.number
where type.type = 'T'
and (action.name like 'create%'
     or action.name like 'Backup%')
and action.type = 'T'